﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Oracle.DataAccess.Client;

namespace CrmApplication.Models
{
    public class CityModels
	{
		#region RegionMappingCity
		
		public decimal Id { get; set; }        
        public decimal IdCounty { get; set; }
        public string Name { get; set; }

		#endregion

		#region RegionOracleConnect
		private static OracleConnection connect;

        static CityModels()
        {
            string connString = System.Configuration.ConfigurationManager.ConnectionStrings["HomeOrcl"].ConnectionString;
            connect = new OracleConnection(connString);
        }

		#endregion

		#region RegionCity
		/// <summary>
        /// select all cities by coresponding county id
        /// </summary>
        /// <param name="searchCity"></param>
        /// <param name="countyId"></param>
        /// <returns></returns>
        public static List<CityModels> GetCity(string searchCity, decimal countyId)
        {
            string selectCounty = "and city_name like '" + searchCity + "%'";
            string selectString = "SELECT id_city,city_name,id_county from nomcity where id_county =:countyId " 
                                    + selectCounty + 
                                    " order by city_name ";

            OracleCommand loginComm = new OracleCommand(selectString);
            loginComm.Parameters.Add(":countyId", countyId);

            loginComm.Connection = connect;

            List<CityModels> result = new List<CityModels>();
            CityModels result1 = null;
            try
            {
                connect.Open();
                using (OracleDataReader sdr = loginComm.ExecuteReader())
                {
                    while (sdr.Read())
                    {
                        result1 = new CityModels();
                        result1.Id = sdr.GetDecimal(0);
                        result1.Name = sdr.GetString(1);
                        result1.IdCounty = sdr.GetDecimal(2);
                        result.Add(result1);
                    }
                }
            }
            finally
            {
                connect.Close();
            }
            return result;
        }

        /// <summary>
        /// check if city already exists
        /// </summary>
        /// <param name="branch"></param>
        /// <returns></returns>
        public static CityModels CheckCity(CityModels city)
        {
            string selectString = "select id_city,city_name from nomcity where city_name=:cityName ";

            OracleCommand loginComm = new OracleCommand(selectString);
            loginComm.Connection = connect;

            loginComm.Parameters.Add(":cityName", city.Name);

            CityModels result = null;

            try
            {
                connect.Open();
                using (OracleDataReader sdr = loginComm.ExecuteReader())
                {
                    while (sdr.Read())
                    {
                        result = new CityModels();
                        result.Id = sdr.GetDecimal(0);
                        result.Name = sdr.GetString(1);
                    }
                }
            }
            finally
            {
                connect.Close();
            }
            return result;
        }

       /// <summary>
       /// select all cities
       /// </summary>
       /// <param name="searchCity"></param>
       /// <returns></returns>
        public static List<CityModels> GetCity(string searchCity)
        {
            string selectCounty = "where city_name like '" + searchCity + "%'";
            string selectString = "SELECT id_city,city_name,id_county from nomcity "
                                    + selectCounty +
                                    " order by city_name ";

            OracleCommand loginComm = new OracleCommand(selectString);
            //loginComm.Parameters.Add(":countyId", countyId);

            loginComm.Connection = connect;

            List<CityModels> result = new List<CityModels>();
            CityModels result1 = null;
            try
            {
                connect.Open();
                using (OracleDataReader sdr = loginComm.ExecuteReader())
                {
                    while (sdr.Read())
                    {
                        result1 = new CityModels();
                        result1.Id = sdr.GetDecimal(0);
                        result1.Name = sdr.GetString(1);
                        result1.IdCounty = sdr.GetDecimal(2);
                        result.Add(result1);
                    }
                }
            }
            finally
            {
                connect.Close();
            }
            return result;
        }

        /// <summary>
        /// add new city 
        /// </summary>
        /// <param name="cityName"></param>
        /// <param name="countyId"></param>
        public static void AddCity(CityModels city)
        {
            string insert = "INSERT INTO nomcity(city_name,id_county)"+ 
                                "VALUES (:cityName,:countyId)";

            OracleCommand usercomm = new OracleCommand(insert);

            usercomm.Parameters.Add(":cityName", city.Name);
            usercomm.Parameters.Add(":countyId", city.IdCounty);

            usercomm.Connection = connect;
            try
            {
                connect.Open();
                usercomm.ExecuteNonQuery();
            }
            finally
            {
                connect.Close();
            }
        }

        /// <summary>
        /// creats city array used with json
        /// </summary>
        /// <param name="searchCity"></param>
        /// <param name="countyId"></param>
        /// <returns></returns>
        public static CityContainer GetCityArray(string searchCity,decimal countyId)
        {
            CityModels[] data = new CityModels[20];
            data = CityModels.GetCity(searchCity, countyId).ToArray();

            CityContainer container = new CityContainer()
            {
                Records = data,
                Count = data.Length
            };

            return container;
        }

        /// <summary>
        /// create all city array for json
        /// </summary>
        /// <param name="searchCity"></param>        
        /// <returns></returns>
        public static CityContainer GetAllCityArray(string searchCity)
        {
            CityModels[] data = new CityModels[20];
            data = CityModels.GetCity(searchCity).ToArray();

            CityContainer container = new CityContainer()
            {
                Records = data,
                Count = data.Length
            };

            return container;
		}
		#endregion
	}

    public class CityContainer
    {
        public CityModels[] Records { get; set; }
        public int Count { get; set; }
    }
}